package com.hanamaki.databaseTest.util;

import com.hanamaki.enumTest.ResultStatus;

import java.sql.*;

public class JDBCUtilTest {
    static Connection connection;
    static Statement statement;
    static ResultSet resultSet;
    static PreparedStatement preparedStatement;


    static void test1() {
        // 通过工具类连接数据库
        try {
            statement = connection.createStatement();
            String sql = "SELECT * from students";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getObject("age"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        // 释放资源
        JDBCUtil.release(connection, resultSet, statement, null);

    }

    /**
     * sql注入
     * 登录  业务
     */
    static void sqlStatement(String username, String password) {

        // 创建 sql执行对象
        try {
            connection = JDBCUtil.getCon();
            statement = connection.createStatement();
            // 编写sql
            String sql = "SELECT * FROM login WHERE username = '" + username + "' AND password = '" + password+"'";
            System.out.println(sql);
            // 执行查询
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                System.out.println("用户名：" + resultSet.getObject("username")
                        + "----->密码" + resultSet.getObject("password"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        // 释放资源
        JDBCUtil.release(connection, resultSet, statement, null);

    }

    static void sqlPrepareStatement(String username, String password) {

        connection = JDBCUtil.getCon();
        // ? 占位符
        String sql = "SELECT * FROM login WHERE username = ? AND password = ?";
        // 创建 sql执行对象
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            resultSet = preparedStatement.executeQuery();
            // 编写sql
            System.out.println(sql);
            // 执行查询
            while (JDBCUtilTest.resultSet.next()) {
                System.out.println("用户名：" + JDBCUtilTest.resultSet.getObject("username")
                        + "----->密码" + JDBCUtilTest.resultSet.getObject("password"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        // 释放资源
        JDBCUtil.release(connection, resultSet, null, preparedStatement);

    }

    /**
     * 增删改   事物
     */
    static void insert(int id, String username, String password) {
        connection = JDBCUtil.getCon();
        String sql = "insert into login(id, username, password) values (?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.setString(2, username);
            preparedStatement.setString(3, password);

            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("添加成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        JDBCUtil.release(connection, null, null, preparedStatement);
    }

    /**
     * 删除
     * @param id
     */
    static void delete(int id) {
        connection = JDBCUtil.getCon();
        String sql = "delete from login where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        JDBCUtil.release(connection, null, null, preparedStatement);
    }

    static void update(int id,String password) {
        connection = JDBCUtil.getCon();
        String sql = "update login set password = ? where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, password);
            preparedStatement.setInt(2, id);

            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                ResultStatus result = ResultStatus.UPDATESUCCESS;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        JDBCUtil.release(connection, null, null, preparedStatement);
    }


    /**
     * 事物编写
     *
     * @param
     */
    static void traction(long money,int send,int accept) throws SQLException {

        connection = JDBCUtil.getCon();
        //关闭事物的自动提交
        try {
            connection.setAutoCommit(false);

            // 编写金钱交易的sql语句
            String sql1 = "update goods set price = price - ? where id = ?";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setObject(1, money);
            preparedStatement.setObject(2, send);
            preparedStatement.executeUpdate();

            int i = 1 / 0;

            String sql2 = "update goods set price = price + ? where id = ?";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setObject(1, money);
            preparedStatement.setObject(2, accept);
            preparedStatement.executeUpdate();

            connection.commit();
            System.out.println("成功");
        } catch (Exception throwables) {
            connection.rollback();
            System.out.println("失败");
            throwables.printStackTrace();
        }finally {
            JDBCUtil.release(connection, null, null, preparedStatement);
        }


    }


    public static void main(String[] args) throws SQLException {

//        test1();
//        sqlStatement(" ' or ' 1=1 ", " ' or ' 1=1 ");
//        System.out.println("=======================");
//        sqlPrepareStatement(" ' or ' 1=1 ", " ' or ' 1=1 ");
//        System.out.println("=======================");
//        sqlPrepareStatement("hanamaki", "123456");
//
//        // 增
//        insert(5, "love", "123321");
//
//        // 删
//        delete(4);
//
//        // 改
//        update(3, "654321");

        traction(401L, 17, 19);

    }



}
